package com.eastdigit.data;

import java.sql.Clob;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

public class QueryBuilder {

    private ArrayList list = new ArrayList();

    private JdbcTemplate jdbcTemplate;

    private String sql = null;

    public QueryBuilder(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public QueryBuilder(JdbcTemplate jdbcTemplate, String sql) {
        this.jdbcTemplate = jdbcTemplate;
        setSQL(sql);
    }

    public QueryBuilder(JdbcTemplate jdbcTemplate, String sql, Object[] params) {
        this.jdbcTemplate = jdbcTemplate;
        setSQL(sql);

        if (params != null) {
            for (Object param : params) {
                addParam(param);
            }
        }
    }

    public void addParam(Object param) {
        list.add(param);
    }

    public void setParams(int index, Object param) {
        list.set(index, param);
    }

    public void setParams(ArrayList list) {
        this.list = list;
    }

    public Object[] getParams() {
        int size = list.size();
        Object[] args = new Object[size];
        for (int i = 0; i < size; i++) {
            args[i] = list.get(i);
        }
        return args;
    }

    public void setSQL(String sql) {
        this.sql = sql;
    }

    public DataTable executeDataTable() {
        return jdbcTemplate.query(sql, getParams(),
                new ResultSetExtractor<DataTable>() {

                    @Override
                    public DataTable extractData(ResultSet rs)
                            throws SQLException, DataAccessException {
                        return new DataTable(rs);
                    }

                });
    }

    public PagedDataTable executePagedDataTable(int pageSize, int pageIndex) {
        PagedDataTable dt = new PagedDataTable();
        try {
            if (pageSize < 1) {
                pageSize = Integer.MAX_VALUE;
            }
            if (pageIndex < 0) {
                pageIndex = 0;
            }
            dt.setTotalRows(getCount());
            dt.setPageIndex(pageIndex);
            dt.setPageSize(pageSize);

            setSQL(getPagedSQL(pageSize, pageIndex));
            dt.setDataTable(executeDataTable());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return dt;
    }

    public Object executeOneValue() {
        return jdbcTemplate.query(sql, getParams(),
                new ResultSetExtractor<Object>() {

                    @Override
                    public Object extractData(ResultSet rs)
                            throws SQLException, DataAccessException {
                        if (rs.next()) {
                            Object t = rs.getObject(1);
                            if (t instanceof Clob) {
                                t = LobUtil.clobToString((Clob) t);
                            }
                            return t;
                        }
                        return null;
                    }

                });
    }

    public String executeString() {
        Object o = executeOneValue();
        if (o == null) {
            return null;
        } else {
            return o.toString();
        }
    }

    public int executeInt() {
        Object o = executeOneValue();
        if (o == null) {
            return 0;
        } else {
            return Integer.parseInt(o.toString());
        }
    }

    public long executeLong() {
        Object o = executeOneValue();
        if (o == null) {
            return 0;
        } else {
            return Long.parseLong(o.toString());
        }
    }

    public boolean checkParams() {
        char[] arr = sql.toCharArray();
        boolean StringCharFlag = false;
        int count = 0;
        for (int i = 0; i < arr.length; i++) {
            char c = arr[i];
            if (c == '\'') {
                if (!StringCharFlag) {
                    StringCharFlag = true;
                } else {
                    StringCharFlag = false;
                }
            } else if (c == '?') {
                if (!StringCharFlag) {
                    count++;
                }
            }
        }
        if (count != list.size()) {
            throw new RuntimeException("SQL中含有" + count + "个参数，但有"
                    + list.size() + "个参数置值!");
        }
        return true;
    }

    public String toString() {
        StringBuffer sb = new StringBuffer();
        sb.append(sql);
        sb.append("\t{");
        for (int i = 0; i < list.size(); i++) {
            if (i != 0) {
                sb.append(",");
            }
            Object o = list.get(i);
            if (o == null) {
                sb.append("null");
                continue;
            }
            String str = list.get(i).toString();
            if (str.length() > 40) {
                str = str.substring(0, 37);
                sb.append(str);
                sb.append("...");
            } else {
                sb.append(str);
            }
        }
        sb.append("}");
        return sb.toString();
    }

    public String getPagedSQL(int pageSize, int pageIndex) throws SQLException {
        StringBuffer sb = new StringBuffer();
        String DBType = DBUtil.getDbType(jdbcTemplate);
        int start = pageIndex * pageSize;
        int end = (pageIndex + 1) * pageSize;
        if (DBType.equals(DBConnConfig.ORACLE)) {
            sb.append("select * from (select rs.*,rownum rnm from (");
            sb.append(sql);
            sb.append(") rs where rownum <= ?) rss where rnm > ?");
            addParam(end);
            addParam(start);
        } else if (DBType.equals(DBConnConfig.DB2)) {
            sb.append("select * from (select rs.*,rownumber() OVER () rnm from (");
            sb.append(sql);
            sb.append(") rs) rss WHERE rnm BETWEEN ? and ?");
            addParam(start + 1);
            addParam(end);
        } else if (DBType.equals(DBConnConfig.MSSQL)) {
            SelectSQLParser sp = new SelectSQLParser();
            try {
                sp.setSQL(sql);
                sp.parse();
            } catch (Exception e) {
                e.printStackTrace();
            }
            sb.append(sp.getMSSQLPagedSQL());
            addParam(start + 1);
            addParam(end);
        } else if (DBType.equals(DBConnConfig.MSSQL2000)) {
            sb.append(sql);
        } else if (DBType.equals(DBConnConfig.MYSQL)) {
            sb.append(sql);
            sb.append(" limit ?,?");
            addParam(start);
            addParam(pageSize);
        }
        return sb.toString();
    }

    public int getCount() {
        QueryBuilder cqb = new QueryBuilder(jdbcTemplate);
        cqb.setParams(list);
        cqb.setSQL("select count(*) from (" + sql + ") t1");
        return cqb.executeInt();
    }

}
